<?php

class Kato_Plugin extends Zend_Controller_Plugin_Abstract {
	
	public $do_kato_request = false;
	
	protected $_db;	
	protected $_map = array();
	protected $_allowVanilla = false;
	protected $_primaryKeys = array();
	
	protected $_view;
	protected $_select;
	protected $_params = array();
	protected $_method;
	protected $_primaryKey;
	
	protected $_table;
	protected $_info;
	
	protected $_id;
	
	protected $_flags = array();
	
	public function __construct($options = array()){

		Zend_Registry::set('Kato_Plugin', $this);
		if (!$options)	return;

		foreach ($options as $key=>$value){
			switch ($key){
				case 'db':
					$this->_db = $value;
					break;
				case 'map':
					$this->_map = $value;
					break;
				case 'allowVanilla':
					$this->_allowVanilla = $value;
					break;
				case 'primaryKeys':
					$this->_primaryKeys = $value;
					break;
				default:
					throw new Exception("key $key not supported by Kato_Plugin");
			}
		}
	}

	
	public function getPrimaryKey(){
		if ($this->_primaryKey){
			return $this->_primaryKey;
		}
		if ($this->_table instanceof Kato_VanillaTable){
			$this->_primaryKey = $this->_table->getPrimary();
			return $this->_primaryKey;
		}
		$primary_key = array();
		$info = $this->_info;
		foreach($info['cols'] as $key=>$value){
			if($info['metadata'][$value]['PRIMARY_POSITION'] !== null){
				$primary_key[$this->_info['metadata'][$value]['PRIMARY_POSITION']] = $value;
			}
		}
		
		$this->_primaryKey = $primary_key;
		return $primary_key;
	}
	
	public function whitelistView($views){
		if (is_string($views)){
			if($this->_view != $views){
				throw new Exception("access denied");
			}
		} else {
			if (!in_array($this->_view, $views)){
				throw new Exception("access denied");				
			}
		}
	}

	public function numToAssoc($rowNum){
		$rowAssoc = array();
		$info = $this->_info;
		
		foreach ($rowNum as $key=>$value){
			$rowAssoc[$info['cols'][$key]] = $value;
		}
		
		return $rowAssoc;
	}
	
	public function whereForRow($rowAssoc){
		$primary_key = $this->getPrimaryKey();
		$where = '';
		
		foreach($primary_key as $col){
			$where .= $this->_table->getAdapter()->quoteInto("$col = ? AND ",$rowAssoc[$col]);
		}
		return $where . 'true';
		
	}
	
	
	public function addParams($params){
		$this->_params = array_merge($this->_params, $params);
	}
	
	public function setParams($params){
		$this->_params = $params;
	}
	
	public function routeStartup(Zend_Controller_Request_Abstract $request){
		
    	if (!$this->getRequest()->isXmlHttpRequest() || !$this->getRequest()->isPost()){
    		$rp = $this->getRequest()->getParams();
    		if (isset($rp['kato_magic'])){
    			$req = $rp['kato_magic'];
    		} else {
    			return;
    		}
    	} else {
    		$req = $request->getRawBody();
    	}
    	
    	$request = Zend_Json::decode($req);
    	$this->_method = $request['method'];
    	$this->_view = $request['params'][0];
    	if(isset($request['params'][1])){
	    	$this->_params = $request['params'][1];
    	}
    	if(isset($request['params'][2])){
	    	$this->_flags = $request['params'][2];
    	}
    	$this->_id = $request['id'];
    	
    	$this->do_kato_request = true;
    }

    public function createTableForView($viewName){    	
    	if(isset($this->_map[$viewName])){
    		$this->_table = new $this->_map[$viewName];
    	} else if ($this->_allowVanilla){
    		$this->_table = new Kato_VanillaTable(array('name'=>$viewName));
    	} else {
    		throw new Exception ("cannot create data source for view '$viewName'");
    	}
    	$this->_info = $this->_table->info();
    }

    public function getPrimaryKeys(){
    	return array(
    		"primary_keys" => $this->_primaryKeys
    	);
    }
    
    public function getData() {
   	try {

    	$columns = array();
    	$primary_key = array();
    	$info = $this->_info;
    	foreach($info['cols'] as $col){
    		$columns[] = array(
    			'num' 					=> $info['metadata'][$col]['COLUMN_POSITION'],
    			'type' 					=> $info['metadata'][$col]['DATA_TYPE'],
    			'len' 					=> $info['metadata'][$col]['LENGTH'],
				'not null' 				=> !$info['metadata'][$col]['NULLABLE'],
				'has default' 			=> $info['metadata'][$col]['DEFAULT'] ? true : false,
    			'column' 				=> $info['metadata'][$col]['COLUMN_NAME'],
				'primary position' 		=> $info['metadata'][$col]['PRIMARY_POSITION']
    		);
    	}

    	$select = $this->_table->select();

    	$limit = null;
    	$offset = null;
  		$orderby = array();
  		
    	foreach($this->_params as $param=>$value){
    		if(!preg_match('/(^[^_]*)_?(.*)$/i', $param, $parts)){
    			throw new Exception ("cannot parse criterion: $param");
    		}
    		
    		$type = $parts[1];
    		
    		if (isset($parts[2])){
	    		$col = $parts[2];
    		}
    		
    		switch($type){
    			case 'search':
    				$select->where("$col = ?", $value!==Null ? $value  : new Zend_Db_Expr("null"));
    				break;
    			case 'regexp':
    			case 'ilike':
    				// FIXME - stinks as hell
    				$pvalue = addslashes($value);
    				$select->where("$col  ilike '%$pvalue%'");
    				break;
                        case 'rregexp':
    				// real regexp
    				$pvalue = addslashes($value);
    				$select->where("$col  ~ '$pvalue'");
    				break;
    			case 'ge':
    				$select->where("$col >= ?", $value);
    				break;
    			case 'gt':
    				$select->where("$col >= ?", $value);
    				break;
    			case 'lt':
    				$select->where("$col < ?", $value);
    				break;
    			case 'le':
    				$select->where("$col <= ?", $value);
    				break;
    			case 'isnull':
    				// FIXME - stinks a bir
    				$select->where("$col is " . ($value == 'true' ? "null" : "not null"));
    				break;
    			case 'orderby':
    				$way = strtolower($value)=='desc' ? 'DESC' : 'ASC';
    				$orderby[] = "$col $way";
    				break;
    			case 'limit':
    				$limit = $value;
    				break;
    			case 'offset':
    				$offset = $value;
    				break;
    		}
    	}
    	
   		// default ordering by primary key columns (just to make it deteriministic)
		if($limit !== null){
    			$select->limit($limit+1, $offset);
		}

		foreach($primary_key as $col){
			$orderby[] = "$col desc";
		}
		
		$select->order($orderby);

    	
		$stmt = $this->_db->query($select);
		$rows = $stmt->fetchAll(Zend_db::FETCH_NUM);

		return array(
			'head' => array('revision' => 0),
			'columns' => $columns,
			'rows' => $rows,
		);
    	} catch (Exception $e){
    		echo $e->getMessage();
    		var_dump($select);
    	}
    }    

    public function deleteRow($rowNum){
    	$rowAssoc = $this->numToAssoc($rowNum);
    	$where = $this->whereForRow($rowAssoc);
    	foreach($this->getPrimaryKey() as $col){
    		unset($rowAssoc[$col]);
    	}
    	$this->_table->delete($where);
    }
    
    public function insertRow($rowNum){
    	$rowAssoc = $this->numToAssoc($rowNum);
    	$this->_table->insert($rowAssoc);
    }
    
    public function updateRow($rowNum){
    	$rowAssoc = $this->numToAssoc($rowNum);
    	$where = $this->whereForRow($rowAssoc);
    	foreach($this->getPrimaryKey() as $col){
    		unset($rowAssoc[$col]);
    	}
    	$this->_table->update($rowAssoc, $where);
    }
    
    
    public function doOperations() {
    	$this->_db->beginTransaction();
    	foreach($this->_params as $row){
                foreach ($row['row'] as &$w){
                        if($w === true) $w = 'true';
                        if($w === false) $w = 'false';  
                }
    		switch($row['command']){
    			case 'delete':
    				$this->deleteRow($row['row']);
    				break;
    			case 'insert':
    				$this->insertRow($row['row']);
    				break;
    			case 'update':
    				$this->updateRow($row['row']);
    				break;
    		}
    	}
    	if (isset($this->_flags['try_only'])) {
    		$this->_db->rollback();
    	} else {
    		$this->_db->commit();
    	}
    }
    
    public function respond_spreadsheet($result, $error, $fname){
            $KContent = new Kato_ContentODS($result);
            $content = $KContent->get_content();
 
            $this->getResponse()
        	->clearAllHeaders()
	    	->setHeader('Pragma','private',true)
                ->setHeader('Cache-Control','no-store',true)
		->setHeader('Content-Type','application/download',true)
		->setHeader('Content-Transfer-Encoding','binary',true)
		->setHeader('Expires','0',true)
		->setHeader('Content-Disposition', 'attachment; filename="'.$fname.'.ods"',true)
        	->setBody($content);                
    }

    public function respond_xls($result, $error, $fname){
            $KContent = new Kato_ContentXLS($result);
            $content = $KContent->get_content();

            $this->getResponse()
        	->clearAllHeaders()
		->setHeader('Content-Disposition', 'attachment; filename="'.$fname.'.xls"',true)
	    	->setHeader('Pragma','private',true)
                ->setHeader('Cache-Control','no-store',true)
		->setHeader('Content-Type','application/download',true)
		->setHeader('Content-Transfer-Encoding','binary',true)
		->setHeader('Expires','0',true)
        	->setBody($content);
    }

    public function respond_csv($result, $error, $fname){
	/*
         * naive CSV
         */
                $o = '';
                $num_col = 0;
                foreach ($result["columns"] as $column) {
                    $o .= '"'.$column["column"].'"';
                    $num_col++;
                    if ( $num_col < count($result["columns"]) ) $o .= ',';
                }
                $o .= "\n";
                foreach ($result["rows"] as $row) {
                    $o .= '"'.implode('","',$row).'"'."\n";
                }

		$this->getResponse()
        	->clearAllHeaders()
	    	->setHeader('Pragma','private',true)
                ->setHeader('Cache-Control','no-store',true)
		->setHeader('Content-Type','application/download',true)
		->setHeader('Content-Transfer-Encoding','binary',true)
		->setHeader('Expires','0',true)
		->setHeader('Content-Disposition', 'attachment; filename="'.$fname.'.csv"',true)
        	->setBody($o);
                                
	}
        
        public function respond_html($result, $error){
	/*
         * builds html document
         */
                $o = "<html>\n<head>";
                $o .= "<meta http-equiv='content-type' content='text/html; charset=utf-8'>\n";
                $o .= "</head>\n";
                $o .= "<style type='text/css'>\n";
                $o .= "  td { border: solid 1px; }\n";
                $o .= "  table { border: solid 1px; }\n";
                $o .= "  th { border: solid 2px; }\n";
                $o .= "</style>\n</head>\n";
                $o .= "<body>\n";
                $o .= "<table>\n<tr>\n";
                foreach ($result["columns"] as $column) {
                    $o .= '<th>'.$column["column"]."</th>\n";
                }
                $o .= "</tr>\n";
                foreach ($result["rows"] as $row) {
                    $o .= "<tr>\n";
                    $o .= '<td>'.implode("</td>\n<td>",$row).'</td>'."\n";
                    $o .= "</tr>\n";
                }
                $o .= "</table>\n</body>\n</html>";
                
		$this->getResponse()
        	->clearAllHeaders()
        	->setHeader("Content-type","text/html; charset=utf-8",true)
        	->setBody($o);
                                
	}
	
	public function respond_plain($result, $error) {
	/*
         * standard JSONRPC respond
         */
		$response = array(
			'id' => $this->_id
		);
		
		if (isset($error)) {
			$response['error'] = $error;
		} else {
			$response['result'] = $result;
		}
        
        $this->getResponse()
        	->clearAllHeaders()
        	->setHeader("Content-type","text/plain; charset=utf-8",true)
        	->setBody(Zend_Json_Encoder::encode($response));    	
    }
    
    public function translate_error($error) {;
    	try {
   			$error_msg = new Zend_Config_Ini('error_msg.ini','messages');
   			$error_translated = '';
   			if (isset($error_msg)) {
   				$errors = $error_msg->toArray();
   				foreach ($errors as $translated_error_message_keyword => $translated_error_messages) {
   					if (stristr($error,$translated_error_message_keyword) !== FALSE) {
   						foreach ($translated_error_messages as $keyword => $message) {
   							if (stristr($error,$keyword) !== FALSE) { $error_translated = $message; }
   						}
   					}
   				}
   			}
   			if ($error_translated == '') { $error_translated = $error; }
   		} catch (Exception $f) {
   			$error_translated = $error;
   		}
   		return $error_translated;
    }
    
	public function dispatchLoopShutdown() {
	try {
		if (!$this->do_kato_request){
    		return;
    	}
    	
    	if (!$this->_table && $this->_view){
    		$this->createTableForView($this->_view);
    	}

	    	switch($this->_method){
	    		case 'get_data':
	    			$result = $this->getData();
	    			break;
	    		case 'do_operations':
	    			$result = $this->doOperations();
	    			break;
	    		case 'get_primary_keys':
	    			$result = $this->getPrimaryKeys();
	    			break;
	    		default:
	    			$result = null;
	    	}
	     	
    } catch (Exception $e){
    	$err = $e->getMessage();
   		$error = $this->translate_error($err);
    }

	    if (null !== ($layout = Zend_Layout::getMvcInstance())) {
            $layout->disableLayout();
        }

        Zend_Controller_Action_HelperBroker::getStaticHelper('viewRenderer')->setNoRender(true);
        
	    switch ($this->_flags['format']) {
	    	case 'spreadsheet':
	    		$this->respond_spreadsheet($result, $error, $this->_flags['name']);
	    		break;
                case 'xls':
	    		$this->respond_xls($result, $error, $this->_flags['name']);
	    		break;
	    	case 'csv':
	    		$this->respond_csv($result, $error, $this->_flags['name']);
	    		break;
	    	case 'html':
	    		$this->respond_html($result, $error);
	    		break;
	    	default:
	    		$this->respond_plain($result, $error);
	    }
	}
	

}
